﻿using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;

using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using Microsoft.SqlServer.Management.Common;

namespace SQLComparer
{
    public class DBManager
    {
        public static Dictionary<string, Different> tableDictionary = new Dictionary<string, Different>();
        public static Connection ConnectionA { get; set; }
        public static Connection ConnectionB { get; set; }
        public DBManager()
        {
            tableDictionary = new Dictionary<string, Different>();
        }
        public void LoadData()
        {
            SqlConnection conn;
            Server server;
            Database database;

            if (ConnectionA != null)
            {

                conn = new SqlConnection(ConnectionA.ConnectionString);

                server = new Server(new ServerConnection(conn));
                database = server.Databases[ConnectionA.Database];

                foreach (Table dbobject in database.Tables)
                {
                    if (dbobject.Schema == "sys")
                    {
                        continue;
                    }
                    string key = dbobject.Schema + dbobject.Name + "Table";

                    if (tableDictionary.ContainsKey(key))
                    {
                        tableDictionary[key].ScriptA = GetString(dbobject.Script());
                        tableDictionary[key].SchemaA = dbobject.Schema;
                        tableDictionary[key].TypeA = "Table";
                        tableDictionary[key].NameA = dbobject.Name;
                    }
                    else
                    {
                        Different d = new Different();
                        d.ScriptA = GetString(dbobject.Script());
                        d.SchemaA = dbobject.Schema;
                        d.TypeA = "Table";
                        d.NameA = dbobject.Name;
                        tableDictionary.Add(key, d);
                    }


                }
                foreach (Microsoft.SqlServer.Management.Smo.View dbobject in database.Views)
                {
                    if (dbobject.Schema == "sys" || dbobject.Schema == "INFORMATION_SCHEMA")
                    {
                        continue;
                    }

                    string key = dbobject.Schema + dbobject.Name + "View";

                    if (tableDictionary.ContainsKey(key))
                    {
                        tableDictionary[key].ScriptA = GetString(dbobject.Script());
                        tableDictionary[key].SchemaA = dbobject.Schema;
                        tableDictionary[key].TypeA = "View";
                        tableDictionary[key].NameA = dbobject.Name;
                    }
                    else
                    {
                        Different d = new Different();
                        d.ScriptA = GetString(dbobject.Script());
                        d.SchemaA = dbobject.Schema;
                        d.TypeA = "View";
                        d.NameA = dbobject.Name;
                        tableDictionary.Add(key, d);
                    }
                }
                foreach (StoredProcedure dbobject in database.StoredProcedures)
                {
                    if (dbobject.Schema == "sys")
                    {
                        continue;
                    }
                    string key = dbobject.Schema + dbobject.Name + "StoredProcedure";

                    if (tableDictionary.ContainsKey(key))
                    {
                        tableDictionary[key].ScriptA = GetString(dbobject.Script());
                        tableDictionary[key].SchemaA = dbobject.Schema;
                        tableDictionary[key].TypeA = "StoredProcedure";
                        tableDictionary[key].NameA = dbobject.Name;
                    }
                    else
                    {
                        Different d = new Different();
                        d.ScriptA = GetString(dbobject.Script());
                        d.SchemaA = dbobject.Schema;
                        d.TypeA = "StoredProcedure";
                        d.NameA = dbobject.Name;
                        tableDictionary.Add(key, d);
                    }
                }
                foreach (UserDefinedFunction dbobject in database.UserDefinedFunctions)
                {
                    if (dbobject.Schema == "sys")
                    {
                        continue;
                    }

                    string key = dbobject.Schema + dbobject.Name + "Function";

                    if (tableDictionary.ContainsKey(key))
                    {
                        tableDictionary[key].ScriptA = GetString(dbobject.Script());
                        tableDictionary[key].SchemaA = dbobject.Schema;
                        tableDictionary[key].TypeA = "Function";
                        tableDictionary[key].NameA = dbobject.Name;
                    }
                    else
                    {
                        Different d = new Different();
                        d.ScriptA = GetString(dbobject.Script());
                        d.SchemaA = dbobject.Schema;
                        d.TypeA = "Function";
                        d.NameA = dbobject.Name;
                        tableDictionary.Add(key, d);
                    }
                }
            }
            if (ConnectionB != null)
            {

                conn = new SqlConnection(ConnectionB.ConnectionString);

                server = new Server(new ServerConnection(conn));
                database = server.Databases[ConnectionB.Database];

                foreach (Table dbobject in database.Tables)
                {
                    if (dbobject.Schema == "sys")
                    {
                        continue;
                    }
                    string key = dbobject.Schema + dbobject.Name + "Table";

                    if (tableDictionary.ContainsKey(key))
                    {
                        tableDictionary[key].ScriptB = GetString(dbobject.Script());
                        tableDictionary[key].SchemaB = dbobject.Schema;
                        tableDictionary[key].TypeB = "Table";
                        tableDictionary[key].NameB = dbobject.Name;
                    }
                    else
                    {
                        Different d = new Different();
                        d.ScriptB = GetString(dbobject.Script());
                        d.SchemaB = dbobject.Schema;
                        d.TypeB = "Table";
                        d.NameB = dbobject.Name;
                        tableDictionary.Add(key, d);
                    }
                }

                foreach (Microsoft.SqlServer.Management.Smo.View dbobject in database.Views)
                {
                    if (dbobject.Schema == "sys" || dbobject.Schema == "INFORMATION_SCHEMA")
                    {
                        continue;
                    }

                    string key = dbobject.Schema + dbobject.Name + "View";

                    if (tableDictionary.ContainsKey(key))
                    {
                        tableDictionary[key].ScriptB = GetString(dbobject.Script());
                        tableDictionary[key].SchemaB = dbobject.Schema;
                        tableDictionary[key].TypeB = "View";
                        tableDictionary[key].NameB = dbobject.Name;
                    }
                    else
                    {
                        Different d = new Different();
                        d.ScriptB = GetString(dbobject.Script());
                        d.SchemaB = dbobject.Schema;
                        d.TypeB = "View";
                        d.NameB = dbobject.Name;
                        tableDictionary.Add(key, d);
                    }
                }
                foreach (StoredProcedure dbobject in database.StoredProcedures)
                {
                    if (dbobject.Schema == "sys")
                    {
                        continue;
                    }

                    string key = dbobject.Schema + dbobject.Name + "StoredProcedure";

                    if (tableDictionary.ContainsKey(key))
                    {
                        tableDictionary[key].ScriptB = GetString(dbobject.Script());
                        tableDictionary[key].SchemaB = dbobject.Schema;
                        tableDictionary[key].TypeB = "StoredProcedure";
                        tableDictionary[key].NameB = dbobject.Name;
                    }
                    else
                    {
                        Different d = new Different();
                        d.ScriptB = GetString(dbobject.Script());
                        d.SchemaB = dbobject.Schema;
                        d.TypeB = "StoredProcedure";
                        d.NameB = dbobject.Name;
                        tableDictionary.Add(key, d);
                    }
                }
                foreach (UserDefinedFunction dbobject in database.UserDefinedFunctions)
                {
                    if (dbobject.Schema == "sys")
                    {
                        continue;
                    }
                    string key = dbobject.Schema + dbobject.Name + "Function";

                    if (tableDictionary.ContainsKey(key))
                    {
                        tableDictionary[key].ScriptB = GetString(dbobject.Script());
                        tableDictionary[key].SchemaB = dbobject.Schema;
                        tableDictionary[key].TypeB = "Function";
                        tableDictionary[key].NameB = dbobject.Name;
                    }
                    else
                    {
                        Different d = new Different();
                        d.ScriptB = GetString(dbobject.Script());
                        d.SchemaB = dbobject.Schema;
                        d.TypeB = "Function";
                        d.NameB = dbobject.Name;
                        tableDictionary.Add(key, d);
                    }
                }
            }
            FormMain.LoadedData(null, null);
        }

        public string GetString(StringCollection sc)
        {
            string ret = string.Empty;
            foreach (string s in sc)
            {
                ret += s + "\r\n";
            }
            return ret;
        }
    }
}
